
import pandas as pd
import json
import re
import os
from typing import List, Dict, Optional


def extract_numbers_from_text(text: Optional[str]) -> Dict[str, Optional[int | float]]:
    """从文本中提取数字信息（年份、人口、面积）"""
    if pd.isna(text) or not isinstance(text, str) or not text.strip():
        return {'year': None, 'population': None, 'area': None}
    
    result = {'year': None, 'population': None, 'area': None}
    
    # 1. 提取年份
    year_patterns = [
        r'(?:公元)?\s*(\d{3,4})\s*年',
        r'([乾隆嘉庆道光咸丰同治光绪宣统顺治康熙雍正洪武永乐嘉靖万历崇祯]+\s*[\u4e00-\u9fa5]*\s*(\d{1,2}))\s*年',
        r'[\D](\d{3,4})[\D]',
    ]
    
    for pattern in year_patterns:
        matches = re.findall(pattern, text)
        if matches:
            for match in matches:
                if isinstance(match, tuple) and len(match) > 1 and match[1].isdigit():
                    year_num = int(match[1])
                    if '乾隆' in match[0]:
                        result['year'] = 1735 + year_num if 1 <= year_num <= 60 else None
                    elif '嘉庆' in match[0]:
                        result['year'] = 1795 + year_num if 1 <= year_num <= 25 else None
                    elif '道光' in match[0]:
                        result['year'] = 1820 + year_num if 1 <= year_num <= 30 else None
                    elif '咸丰' in match[0]:
                        result['year'] = 1850 + year_num if 1 <= year_num <= 11 else None
                    elif '同治' in match[0]:
                        result['year'] = 1861 + year_num if 1 <= year_num <= 13 else None
                    elif '光绪' in match[0]:
                        result['year'] = 1874 + year_num if 1 <= year_num <= 34 else None
                    elif '宣统' in match[0]:
                        result['year'] = 1908 + year_num if 1 <= year_num <= 3 else None
                    elif '顺治' in match[0]:
                        result['year'] = 1643 + year_num if 1 <= year_num <= 18 else None
                    elif '康熙' in match[0]:
                        result['year'] = 1661 + year_num if 1 <= year_num <= 61 else None
                    elif '雍正' in match[0]:
                        result['year'] = 1722 + year_num if 1 <= year_num <= 13 else None
                    elif '洪武' in match[0]:
                        result['year'] = 1367 + year_num if 1 <= year_num <= 31 else None
                    elif '永乐' in match[0]:
                        result['year'] = 1402 + year_num if 1 <= year_num <= 22 else None
                    elif '嘉靖' in match[0]:
                        result['year'] = 1521 + year_num if 1 <= year_num <= 45 else None
                    elif '万历' in match[0]:
                        result['year'] = 1572 + year_num if 1 <= year_num <= 48 else None
                    elif '崇祯' in match[0]:
                        result['year'] = 1627 + year_num if 1 <= year_num <= 17 else None
                    elif '至元' in match[0]:
                        result['year'] = 1264 + year_num if 1 <= year_num <= 31 else None
                    break
                elif isinstance(match, str) and match.isdigit():
                    year_val = int(match)
                    if 0 < year_val < 2020:
                        result['year'] = year_val
                        break
        if result['year']:
            break
    
    # 2. 提取人口数量
    population_patterns = [
        r'(\d+(?:\.\d+)?)\s*万户',
        r'(\d+(?:\.\d+)?)\s*万口',
        r'(\d+(?:\.\d+)?)\s*万人',
        r'人口.*?(\d+(?:\.\d+)?)\s*万',
        r'户.*?(\d+(?:\.\d+)?)\s*万',
        r'(\d+)\s*户',
        r'(\d+)\s*口',
        r'人口.*?(\d+(?:,\d+)*)',
        r'丁口.*?(\d+(?:\.\d+)?)\s*万',
    ]
    
    for pattern in population_patterns:
        match = re.search(pattern, text)
        if match:
            try:
                num_str = match.group(1).replace(',', '')
                num = float(num_str)
                if '万' in pattern:
                    result['population'] = int(num * 10000)
                elif '户' in pattern:
                    result['population'] = int(num * 5)
                else:
                    result['population'] = int(num)
                if result['population'] > 100000000:
                    result['population'] = None
                break
            except:
                continue
    
    # 3. 提取面积数据
    area_patterns = [
        r'面积.*?(\d+(?:\.\d+)?)\s*(?:万\s*)?平方(?:千米|公里)',
        r'(\d+(?:\.\d+)?)\s*(?:万\s*)?平方(?:千米|公里)',
        r'耕地.*?(\d+(?:\.\d+)?)\s*(?:万\s*)?亩',
        r'面积.*?(\d+(?:\.\d+)?)\s*(?:万\s*)?亩',
        r'(\d+(?:\.\d+)?)\s*(?:万\s*)?顷',
    ]
    
    for pattern in area_patterns:
        match = re.search(pattern, text)
        if match:
            try:
                area_value = float(match.group(1))
                if '万平方' in pattern:
                    result['area'] = round(area_value * 10000, 2)
                elif '平方' in pattern:
                    result['area'] = round(area_value, 2)
                elif '万亩' in pattern:
                    result['area'] = round(area_value * 10000 / 1500, 2)
                elif '亩' in pattern:
                    result['area'] = round(area_value / 1500, 2)
                elif '万顷' in pattern:
                    result['area'] = round(area_value * 10000 * 0.01, 2)
                elif '顷' in pattern:
                    result['area'] = round(area_value * 0.01, 2)
                if result['area'] > 1000000:
                    result['area'] = None
                break
            except:
                continue
    
    return result


def extract_data_from_excel(file_path: str) -> List[Dict[str, Optional[str | int | float]]]:
    """从Excel文件的所有工作表中提取建制沿革数据"""
    try:
        if not os.path.exists(file_path):
            print(f"错误：文件不存在 - {file_path}")
            return []
        
        excel_file = pd.ExcelFile(file_path)
        all_results = []
        
        print(f"发现 {len(excel_file.sheet_names)} 个工作表，开始逐个处理...")
        
        for sheet_name in excel_file.sheet_names:
            print(f"\n正在处理工作表: {sheet_name}")
            
            try:
                df = pd.read_excel(file_path, sheet_name=sheet_name)
                
                df.columns = [
                    str(col).strip().replace('Unnamed: ', '列_') if 'Unnamed' in str(col) 
                    else str(col).strip() 
                    for col in df.columns
                ]
                
                period_col = None
                content_col = None
                
                for col in df.columns:
                    if any(keyword in str(col) for keyword in ['时期', '朝代', '时代']):
                        period_col = col
                    elif any(keyword in str(col) for keyword in ['描述', '原文', '内容', '建制', '三级', '四级']):
                        content_col = col
                
                if period_col is None and len(df.columns) > 0:
                    period_col = df.columns[0]
                    print(f"  自动分配时期列: {period_col}")
                else:
                    print(f"  识别的时期列: {period_col}")
                
                if content_col is None and len(df.columns) > 1:
                    content_col = df.columns[1]
                    print(f"  自动分配内容列: {content_col}")
                else:
                    print(f"  识别的内容列: {content_col}")
                
                if period_col not in df.columns or content_col not in df.columns:
                    print(f"  警告：工作表 {sheet_name} 缺少关键列，跳过处理")
                    continue
                
                valid_records = 0
                for idx, row in df.iterrows():
                    period_raw = row[period_col]
                    period = str(period_raw).strip() if not pd.isna(period_raw) else None
                    
                    content_raw = row[content_col]
                    content = str(content_raw).strip() if not pd.isna(content_raw) else None
                    
                    if period and content and len(content) > 1:
                        extracted_data = extract_numbers_from_text(content)
                        
                        record = {
                            'category': period,
                            'sheet_name': sheet_name,
                            'original_text': content[:500] + '...' if len(content) > 500 else content,
                            'year': extracted_data['year'],
                            'population': extracted_data['population'],
                            'area': extracted_data['area'],
                            'row_index': idx + 1,
                            'extraction_time': pd.Timestamp.now().strftime('%Y-%m-%d %H:%M:%S')
                        }
                        
                        all_results.append(record)
                        valid_records += 1
                
                print(f"  处理完成：{valid_records} 条有效记录")
                
            except Exception as sheet_err:
                print(f"  处理工作表 {sheet_name} 时出错: {str(sheet_err)}")
                continue
        
        print(f"\n所有工作表处理完成！共提取到 {len(all_results)} 条有效记录")
        return all_results
    
    except Exception as e:
        print(f"处理Excel文件时发生严重错误: {str(e)}")
        import traceback
        traceback.print_exc()
        return []


def save_data_to_json(data: List[Dict], output_file: str, indent: int = 2) -> bool:
    """将提取的数据保存为JSON文件"""
    try:
        output_dir = os.path.dirname(output_file)
        if output_dir and not os.path.exists(output_dir):
            os.makedirs(output_dir, exist_ok=True)
            print(f"创建输出目录: {output_dir}")
        
        with open(output_file, 'w', encoding='utf-8') as f:
            json.dump(data, f, ensure_ascii=False, indent=indent, default=str)
        
        if os.path.exists(output_file):
            file_size = os.path.getsize(output_file) / 1024
            print(f"\n数据成功保存到: {output_file}")
            print(f"文件大小: {file_size:.2f} KB")
            return True
        else:
            print(f"错误：文件保存后未找到 - {output_file}")
            return False
    
    except Exception as e:
        print(f"保存JSON文件时出错: {str(e)}")
        return False


def analyze_extracted_data(data: List[Dict]) -> None:
    """分析提取的数据质量，生成统计报告"""
    if not data:
        print("没有可分析的数据")
        return
    
    print("\n" + "="*70)
    print("建制沿革数据提取质量分析报告")
    print("="*70)
    
    # 1. 总体统计
    print(f"\n1. 总体统计")
    print(f"   总记录数: {len(data):,}")
    print(f"   数据提取时间范围: {data[0]['extraction_time']} 至 {data[-1]['extraction_time']}")
    
    # 2. 时期/朝代分布
    category_counts = {}
    for record in data:
        cat = record['category']
        category_counts[cat] = category_counts.get(cat, 0) + 1
    
    print(f"\n2. 时期/朝代分布（前20名）")
    sorted_categories = sorted(category_counts.items(), key=lambda x: x[1], reverse=True)[:20]
    for i, (cat, count) in enumerate(sorted_categories, 1):
        percentage = (count / len(data)) * 100
        print(f"   {i:2d}. {cat:<12} {count:5d} 条 ({percentage:5.1f}%)")
    
    # 3. 数据完整性
    year_available = sum(1 for record in data if record['year'] is not None)
    population_available = sum(1 for record in data if record['population'] is not None)
    area_available = sum(1 for record in data if record['area'] is not None)
    
    print(f"\n3. 数据完整性统计")
    print(f"   包含年份数据: {year_available:5d} 条 ({(year_available/len(data))*100:5.1f}%)")
    print(f"   包含人口数据: {population_available:5d} 条 ({(population_available/len(data))*100:5.1f}%)")
    print(f"   包含面积数据: {area_available:5d} 条 ({(area_available/len(data))*100:5.1f}%)")
    
    # 4. 年份分析
    valid_years = [record['year'] for record in data if record['year'] is not None and 0 < record['year'] < 2100]
    if valid_years:
        print(f"\n4. 年份数据分析")
        print(f"   年份范围: {min(valid_years)} - {max(valid_years)}")
        print(f"   平均年份: {sum(valid_years)/len(valid_years):.0f}")
        print(f"   中位数年份: {sorted(valid_years)[len(valid_years)//2]}")
    
    # 5. 人口分析
    valid_populations = [record['population'] for record in data if record['population'] is not None and record['population'] > 0]
    if valid_populations:
        print(f"\n5. 人口数据分析")
        print(f"   人口数量范围: {min(valid_populations):,} - {max(valid_populations):,} 人")
        print(f"   平均人口数量: {sum(valid_populations)/len(valid_populations):,.0f} 人")
    
    # 6. 面积分析
    valid_areas = [record['area'] for record in data if record['area'] is not None and record['area'] > 0]
    if valid_areas:
        print(f"\n6. 面积数据分析")
        print(f"   面积范围: {min(valid_areas):.2f} - {max(valid_areas):.2f} 平方千米")
        print(f"   平均面积: {sum(valid_areas)/len(valid_areas):.2f} 平方千米")
    
    # 7. 来源分布
    sheet_counts = {}
    for record in data:
        sheet = record['sheet_name']
        sheet_counts[sheet] = sheet_counts.get(sheet, 0) + 1
    
    print(f"\n7. 来源工作表分布")
    for sheet, count in sorted(sheet_counts.items()):
        percentage = (count / len(data)) * 100
        print(f"   {sheet:<15} {count:5d} 条 ({percentage:5.1f}%)")
    
    print("\n" + "="*70)


def load_and_verify_json(json_file: str) -> Optional[List[Dict]]:
    """加载并验证JSON文件数据"""
    try:
        if not os.path.exists(json_file):
            print(f"错误：JSON文件不存在 - {json_file}")
            return None
        
        with open(json_file, 'r', encoding='utf-8') as f:
            data = json.load(f)
        
        if not isinstance(data, list):
            print(f"错误：JSON文件格式不正确，预期列表类型")
            return None
        
        print(f"成功加载JSON文件: {json_file}")
        print(f"包含 {len(data)} 条记录")
        
        if data:
            sample_record = data[0]
            required_fields = ['category', 'sheet_name', 'original_text', 'row_index']
            missing_fields = [field for field in required_fields if field not in sample_record]
            
            if missing_fields:
                print(f"警告：记录缺少必要字段: {', '.join(missing_fields)}")
            else:
                print("记录字段验证通过")
        
        return data
    
    except Exception as e:
        print(f"加载JSON文件时出错: {str(e)}")
        return None


def main():
    """主函数：执行完整的数据提取流程"""
    print("="*70)
    print("建制沿革Excel数据提取工具 - 开始运行")
    print("="*70)
    
    # 配置参数
    config = {
        'input_excel': './data/07建制沿革 - 总数据和各朝代数据.xlsx',
        'output_json': './outputs/Evolution_of_Organizational_Structure_data.json',
        'perform_analysis': True,
        'verify_output': True
    }
    
    print(f"\n当前配置:")
    for key, value in config.items():
        print(f"  {key}: {value}")
    
    # 1. 提取数据
    print(f"\n" + "-"*50)
    print("步骤1：从Excel文件提取数据")
    print("-"*50)
    extracted_data = extract_data_from_excel(config['input_excel'])
    
    if not extracted_data:
        print("数据提取失败，程序终止")
        return
    
    # 2. 保存数据
    print(f"\n" + "-"*50)
    print("步骤2：保存数据到JSON文件")
    print("-"*50)
    save_success = save_data_to_json(extracted_data, config['output_json'])
    
    if not save_success:
        print("JSON文件保存失败，程序终止")
        return
    
    # 3. 数据分析
    if config['perform_analysis']:
        print(f"\n" + "-"*50)
        print("步骤3：执行数据质量分析")
        print("-"*50)
        analyze_extracted_data(extracted_data)
    
    # 4. 验证文件
    if config['verify_output']:
        print(f"\n" + "-"*50)
        print("步骤4：验证输出JSON文件")
        print("-"*50)
        verified_data = load_and_verify_json(config['output_json'])
        
        if verified_data and len(verified_data) == len(extracted_data):
            print("输出文件验证成功：数据完整性一致")
        else:
            print("警告：输出文件验证存在问题，数据可能不完整")
    
    # 5. 显示示例
    print(f"\n" + "-"*50)
    print("步骤5：显示示例数据（前3条）")
    print("-"*50)
    
    sample_count = min(3, len(extracted_data))
    for i, record in enumerate(extracted_data[:sample_count], 1):
        print(f"\n示例 {i}:")
        print(f"  时期/朝代: {record['category']}")
        print(f"  来源工作表: {record['sheet_name']}")
        print(f"  年份: {record['year'] if record['year'] else '未提取到'}")
        
        if record['population']:
            print(f"  人口: {record['population']:,} 人")
        else:
            print(f"  人口: 未提取到")
        
        if record['area']:
            print(f"  面积: {record['area']:.2f} 平方千米")
        else:
            print(f"  面积: 未提取到")
        
        print(f"  原始行号: 第{record['row_index']}行")
        
        if len(record['original_text']) > 150:
            print(f"  原文预览: {record['original_text'][:150]}...")
        else:
            print(f"  原文预览: {record['original_text']}")
        
        print("-" * 60)
    
    print(f"\n" + "="*70)
    print("建制沿革数据提取工具 - 运行完成")
    print(f"最终结果文件: {os.path.abspath(config['output_json'])}")
    print("="*70)


if __name__ == '__main__':
    import sys
    if sys.platform.startswith('win'):
        import io
        sys.stdout = io.TextIOWrapper(sys.stdout.buffer, encoding='utf-8')
    
    main()
